{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# AdventureWorks - Easy\n",
    "\n",
    "![rel](https://sqlzoo.net/w/images/2/28/AdventureWorks.png)\n",
    "\n",
    "> Note: the table `Customer` should be renamed as `CustomerAW` to avoid conflicts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prerequesites\n",
    "from pyhive import hive\n",
    "%load_ext sql\n",
    "%sql hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.\n",
    "**Show the first name and the email address of customer with CompanyName 'Bike World'**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>firstname</th>\n",
       "        <th>emailaddress</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Kerim</td>\n",
       "        <td>kerim0@adventure-works.com</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Kerim', 'kerim0@adventure-works.com')]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT FirstName, EmailAddress FROM CustomerAW\n",
    "  WHERE CompanyName='Bike World'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.\n",
    "**Show the CompanyName for all customers with an address in City 'Dallas'.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>companyname</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Elite Bikes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Rental Bikes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Third Bike Store</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Town Industries</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Unsurpassed Bikes</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Elite Bikes',),\n",
       " ('Rental Bikes',),\n",
       " ('Third Bike Store',),\n",
       " ('Town Industries',),\n",
       " ('Unsurpassed Bikes',)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT DISTINCT CompanyName\n",
    "  FROM CustomerAW JOIN CustomerAddress ON (\n",
    "      CustomerAW.CustomerID=CustomerAddress.CustomerID)\n",
    "    JOIN Address ON (CustomerAddress.AddressID=Address.AddressID)\n",
    "    WHERE Address.City='Dallas'\n",
    "    ORDER BY CompanyName"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.\n",
    "**How many items with ListPrice more than $1000 have been sold?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>qty</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>451</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(451,)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT SUM(OrderQty) qty\n",
    "  FROM SalesOrderDetail JOIN Product ON (\n",
    "      SalesOrderDetail.ProductID=Product.ProductID)\n",
    "    WHERE ListPrice>1000"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.\n",
    "**Give the CompanyName of those customers with orders over $100000. Include the subtotal plus tax plus freight.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>CompanyName</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Action Bicycle Specialists</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Metropolitan Bicycle Supply</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Action Bicycle Specialists',), ('Metropolitan Bicycle Supply',)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT DISTINCT CompanyName\n",
    "  FROM SalesOrderHeader JOIN CustomerAW ON (\n",
    "      SalesOrderHeader.CustomerID=CustomerAW.CustomerID)\n",
    "    WHERE COALESCE(SubTotal, 0) + COALESCE(TaxAmt, 0) +\n",
    "      COALESCE(Freight, 0) > 100000"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.\n",
    "**Find the number of left racing socks ('Racing Socks, L') ordered by CompanyName 'Riding Cycles'**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>total</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(3,)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT SUM(OrderQty) total\n",
    "  FROM SalesOrderDetail JOIN Product ON (\n",
    "    SalesOrderDetail.ProductID=Product.ProductID) JOIN\n",
    "    SalesOrderHeader ON (\n",
    "        SalesOrderHeader.SalesOrderID=SalesOrderDetail.SalesOrderID) JOIN\n",
    "    CustomerAW ON (\n",
    "        SalesOrderHeader.CustomerID=CustomerAW.CustomerID)\n",
    "    WHERE Product.Name='Racing Socks, L' AND \n",
    "        CompanyName='Riding Cycles'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
